const db = require('../config/db');

const createOrder = async (userId, items, totalAmount, address) => {
  const conn = await db.getConnection();
  
  try {
    await conn.beginTransaction();
    
    // 创建订单
    const [orderResult] = await conn.query(
      'INSERT INTO orders (user_id, total_amount, status, address) VALUES (?, ?, "pending", ?)',
      [userId, totalAmount, address]
    );
    const orderId = orderResult.insertId;
    
    // 添加订单项
    for (const item of items) {
      await conn.query(
        'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
        [orderId, item.product_id, item.quantity, item.price]
      );
    }
    
    // 清空购物车
    await conn.query('DELETE FROM cart WHERE user_id = ?', [userId]);
    
    await conn.commit();
    return orderId;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
};

const getOrdersByUserId = async (userId) => {
  const [orders] = await db.query(
    'SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC',
    [userId]
  );
  
  for (const order of orders) {
    const [items] = await db.query(
      `SELECT oi.*, p.name, p.image 
       FROM order_items oi 
       JOIN products p ON oi.product_id = p.id 
       WHERE oi.order_id = ?`,
      [order.id]
    );
    order.items = items;
  }
  
  return orders;
};

const getOrderById = async (orderId, userId) => {
  const [orders] = await db.query(
    'SELECT * FROM orders WHERE id = ? AND user_id = ?',
    [orderId, userId]
  );
  
  if (orders.length === 0) {
    return null;
  }
  
  const order = orders[0];
  const [items] = await db.query(
    `SELECT oi.*, p.name, p.image 
     FROM order_items oi 
     JOIN products p ON oi.product_id = p.id 
     WHERE oi.order_id = ?`,
    [order.id]
  );
  
  order.items = items;
  return order;
};

module.exports = {
  createOrder,
  getOrdersByUserId,
  getOrderById
};